package dmg_projekt;

import java.sql.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import javax.swing.*;

/**
 *
 * @author Philipp Ryser <philipp.ryser@hslu.ch>
 */
public class ConnectionHandler
{

    private Connection conn = null;
    private String User = "sa";
    private String Instance = "localhost\\dmgdb";
    private String Database = "fertigung"; //fertig_test2, oder fertig_testbed oder uni
    private String Password = "1234";
    private String DB_URL = "jdbc:sqlserver://";


    public ConnectionHandler()
    {

    }

    public void connectToDB() throws SQLException
    {
        String Connection = "";
        Connection = DB_URL + Instance + ";" + "Database=" + Database;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection(Connection, User, Password);
            //conn = DriverManager.getConnection("jdbc:sqlserver://localhost\\dmgdb;Database=uni", "sa", "pw");

            if (getConn() != null) {

                System.out.println("lucky: connection ok");
                //now do stuff...

                //conn.setAutoCommit(false); //TA-Handling
                selectTest(conn);
                //insertTest(conn);
                //dateDbProcTest(conn);
                //conn.commit();
            }

        } catch (Exception ex) {
            System.out.println(ex.getMessage());

        } finally {
            System.out.println("closing");

            if (getConn() != null) {
                getConn().close();
            }
        }
    }

    /*
     * returns connection obj
     */
    public Connection getConn()
    {
        return conn;
    }

    /*
     * Testmethode select
     */
    protected static void selectTest(Connection conn)
    {
        try {
            // Create a Statement
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from maschinen");
            //ResultSet rs = stmt.executeQuery("select * from studenten");

            while (rs.next()) {
                System.out.println(rs.getString(1) + "/" + rs.getString("wartungsintervall") + "/" + rs.getString("wirdGewartetVon"));
                //System.out.println(rs.getInt("matrNr") + "/" + rs.getString("name") + "/" + rs.getInt("semester"));
            }
            stmt.close();
            System.out.println("very lucky, good bye");

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }

    /*
     * Testmethode insert
     */
    protected static void insertTest(Connection conn)
    {
        //TODO: machwas
    }

    /*
     * Testmethode procedure
     */
    protected static void dateDbProcTest(Connection conn)
    {
        //TODO: machwas
    }
}
